package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Project;
import model.ProjectSubContractor;
import model.SubContractor;

public class DBProjectSubContractor implements IFProjectSubContractor
	{
		private Connection con;

		public DBProjectSubContractor()
		{

			con = DBConnection.getInstance().getDBcon();
		}

	@Override
	public int addProjectSubContractor(Project project, SubContractor sub,
			double price) throws SQLException
		{
			int rc = -1;
			String query = "SELECT ps_id FROM project_subcontractor WHERE ps_project_id = ? AND ps_outsider_id = ?";
			
			if(DBConnection.doDebug())
				DBConnection.printDebug("addProjectSubContractor", query);
			
			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setInt(1, project.getServiceID());
					ps.setInt(2, sub.getOutsiderID());
					
					ResultSet results = ps.executeQuery();
					
					if(!results.next())
						{
							query = "INSERT INTO project_subcontractor(ps_project_id, ps_outsider_id, ps_price) VALUES(?,?,?)";
							
							try
								{
							if(DBConnection.doDebug())
								DBConnection.printDebug("addProjectSubContractor", query);
							
							ps = con.prepareStatement(query);
							
							ps.setInt(1, project.getServiceID());
							ps.setInt(2, sub.getOutsiderID());
							ps.setDouble(3, price);
							
							rc = ps.executeUpdate();
								}
							catch(Exception e)
								{
									if (DBConnection.doDebug())
										{
											DBConnection.printDebug("addProjectSubContractor", query);
											e.printStackTrace();
										} 
								}
						} // end of if
					
				} catch(Exception e)
					{
						if (DBConnection.doDebug())
							{
								DBConnection.printDebug("addProjectSubContractor", query);
								e.printStackTrace();
							} 
					}
			
			return rc;
		}

	@Override
	public int deleteProjectSubContractor(Project project, int subID)
			throws SQLException
		{
			int rc = -1;
			String query = "SELECT ps_id, ps_price FROM project_subContractor WHERE ps_project_id = ? AND ps_outsider_id = ?";
			
			if(DBConnection.doDebug())
				DBConnection.printDebug("deleteProjectSubContractor", query);
			
			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setInt(1, project.getServiceID());
					ps.setInt(2,  subID);
					
					ResultSet results = ps.executeQuery();
					
					if(results.next())
						{
							query = "DELETE FROM project_subContractor WHERE ps_id = ?";
							
							if(DBConnection.doDebug())
								DBConnection.printDebug("deleteProjectSubContractor", query);
							
							try
								{
									ps = con.prepareStatement(query);
									
									ps.setInt(1, results.getInt("ps_id"));
									
									rc = ps.executeUpdate();
									
									
								}
							catch(Exception e)
								{
									if(DBConnection.doDebug())
										{
											DBConnection.printDebug("deleteProjectSubContractor", query);
											e.printStackTrace();
										}
								}
						}
					ps.close();
				}
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("deleteProjectSubContractor", query);
							e.printStackTrace();
						}
				}
			
			return rc;
		}

	@Override
	public int updateProjectSubContractor(Project project, int subID,
			double price) throws SQLException
		{
			int rc = -1;
			String query = "SELECT ps_id, ps_price FROM project_subContractor WHERE ps_project_id = ? AND ps_outsider_id = ?";
			
			if(DBConnection.doDebug())
				DBConnection.printDebug("updateProjectSubContractor", query);
			
			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setInt(1, project.getServiceID());
					ps.setInt(2, subID);
					
					ResultSet results = ps.executeQuery();
					
					if(results.next())
						{
							query = "UPDATE project_subContractor SET ps_price = ? WHERE ps_id = ?";
							
							if(DBConnection.doDebug())
								DBConnection.printDebug("updateProjectSubContractor", query);
							
							try
								{
									ps = con.prepareStatement(query);
									
									ps.setDouble(1, price);
									ps.setInt(2,results.getInt("ps_id"));
									
									rc = ps.executeUpdate();
									
									
								}
							catch(Exception e)
								{
									if(DBConnection.doDebug())
										{
											DBConnection.printDebug("updateProjectSubContractor", query);
										}
								}
						}
					ps.close();
				}
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("updateProjectSubContractor", query);
							e.printStackTrace();
						}
				}
			return rc;
		}

	@Override
	public ArrayList<ProjectSubContractor> getAllProjectSubContractor(Project project)
			throws SQLException
		{
			ArrayList <ProjectSubContractor> psList = new ArrayList<ProjectSubContractor>();
			
			String query = "SELECT ps.ps_price, o.outsider_id, o.outsider_name FROM project_subContractor AS ps LEFT JOIN outsider AS o ON ps.ps_outsider_id = o.outsider_id WHERE ps.ps_project_id = ? ";
	
			if(DBConnection.doDebug())
				DBConnection.printDebug("getAllProjectSubContractor", query);
			
			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setInt(1, project.getServiceID());
					
					ResultSet result = ps.executeQuery();
							
							while(result.next())
								{
									ProjectSubContractor newPs = new ProjectSubContractor();
									SubContractor sub = new SubContractor();
									
									sub.setOutsiderID(result.getInt("outsider_id"));
									sub.setName(result.getString("outsider_name"));
									
									newPs.setPrice(result.getDouble("ps_price"));
									newPs.setProj(project);
									newPs.setSub(sub);
									
									psList.add(newPs);
								}
					
					ps.close();
				}
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getAllProjectSubContractor", query);
							e.printStackTrace();
						}
				}
			
			return psList;
		}

	@Override
	public double getTotalProjectSubContractorPrice(int projectID)
			throws SQLException
		{
			double totalContractorPrice = 0.0;
			
			String query = " SELECT COUNT(ps_outsider_id) AS number_of_subs FROM project_subContractor WHERE ps_project_id = ?;" +
					"SELECT SUM(ps_price * number_of_subs) AS total_project_cost FROM project_subContractor WHERE ps_project_id = ?;";
		
			if(DBConnection.doDebug())
				DBConnection.printDebug("getTotalProjectSubContractorPrice", query);
			
			try
				{
					PreparedStatement ps = con.prepareStatement(query);
					
					ps.setInt(1, projectID);
					ps.setInt(2, projectID);
					
					ResultSet result = ps.executeQuery();
					
					if(result.next())
						totalContractorPrice = result.getDouble("total_project_cost");
					
					ps.close();
				}
			catch(Exception e)
				{
					if(DBConnection.doDebug())
						{
							DBConnection.printDebug("getTotalProjectSubContractorPrice", query);
							e.printStackTrace();
						}
				}
		return totalContractorPrice;
		}

	}
